package com.ids.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.ids.bean.UserCreationType;
import com.ids.util.DbManager;
import com.mysql.jdbc.Statement;

public class UserCreationScreenDao {
	private static UserCreationScreenDao instance = new UserCreationScreenDao();

	private UserCreationScreenDao() {}

	public static UserCreationScreenDao getInstance() {
		return instance;
	}

	public static void main(String[] args) {
		UserCreationScreenDao aa = new UserCreationScreenDao();
		aa.getReportingTo();
	}

	public List<UserCreationType> getDesignationId() {
		DbManager dbManager = new DbManager();  
		Connection conn = dbManager.getConnection();
		ResultSet userCreationTypeRS = null;
		String designationTypeSql = "select * from designations";
		Statement state = null;
		List<UserCreationType> designationId = null;
		try {
			state = (Statement) conn.createStatement();
			userCreationTypeRS = state.executeQuery(designationTypeSql);
			designationId = new ArrayList<UserCreationType>();
			UserCreationType userCreationTypeBeans = null;
			while (userCreationTypeRS.next()) {
				userCreationTypeBeans = new UserCreationType();
				userCreationTypeBeans.setId(userCreationTypeRS
						.getString("designationId"));
				userCreationTypeBeans.setValue(userCreationTypeRS
						.getString("designationName"));
				designationId.add(userCreationTypeBeans);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbManager.closeResultSet(userCreationTypeRS);
			DbManager.closeConnection(conn);
		}
		return designationId;
	}

	public List<UserCreationType> getRollId() {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		ResultSet userCreationTypeRS = null;
		String rollIdTypeSql = "select * from roles";
		Statement state = null;
		List<UserCreationType> roleId = null;
		try {
			state = (Statement) conn.createStatement();
			userCreationTypeRS = state.executeQuery(rollIdTypeSql);
			roleId = new ArrayList<UserCreationType>();
			UserCreationType userCreationBeans = null;
			while (userCreationTypeRS.next()) {
				userCreationBeans = new UserCreationType();
				userCreationBeans.setId(userCreationTypeRS.getString("roleId"));
				userCreationBeans.setValue(userCreationTypeRS.getString("roleName"));
				roleId.add(userCreationBeans);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbManager.closeResultSet(userCreationTypeRS);
			DbManager.closeConnection(conn);
		}
		return roleId;
	}

	public List<UserCreationType> getReportingTo() {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		ResultSet userCreationTypeRS = null;
		String reportingToTypeSql = "select * from employees";
		Statement state = null;
		List<UserCreationType> reportingTo = null;
		try {
			state = (Statement) conn.createStatement();
			userCreationTypeRS = state.executeQuery(reportingToTypeSql);
			reportingTo = new ArrayList<UserCreationType>();
			UserCreationType userCreationTypeBeans = null;
			while (userCreationTypeRS.next()) {
				userCreationTypeBeans = new UserCreationType();
				userCreationTypeBeans.setId(userCreationTypeRS.getString("empCode"));
				userCreationTypeBeans.setValue(userCreationTypeRS.getString("firstName")
						+ "" + userCreationTypeRS.getString("lastName"));
				reportingTo.add(userCreationTypeBeans);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbManager.closeResultSet(userCreationTypeRS);
			DbManager.closeConnection(conn);
		}
		return reportingTo;
	}

}
